package com.lysj.order.order.repository;

import com.lysj.admin.utils.spring.foundation.base.BaseRepository;
import com.lysj.order.order.domain.Order;
import com.lysj.order.order.vo.OrderVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @author Created by drj on 2019-03-07 16:36:33
 * @description 订单dao
 */
public interface OrderRepository extends BaseRepository<Order> {

    //查询正在开桌的订单
    Order findByDeskIdAndStatusAndDelFlag(String deskId, Integer status, Integer delFlag);


    //根据门店id查找
    List<Order> findByStoreIdAndDelFlagAndStatus(String storeId, Integer delFlag, Integer status);

    //根据用户id查找
    List<Order> findByCustomerIdAndDelFlagOrderByCreateTimeDesc(String customerId, Integer delFlag);

    //查询用户正在点餐的订单
    Order findByCustomerIdAndStatusAndDelFlag(String customerId, Integer status, Integer delFlag);

    //后台订单查询
    @Query(value = "SELECT NEW com.lysj.order.order.vo.OrderVo(o.id,o.orderNumber,o.createTime,o.deskId,o.customerId,o.actPayMoney,o.payWay,o.status,o.cusAmount) " +
            "FROM  Order o WHERE o.storeId=:storeId AND o.orderNumber  LIKE CONCAT('%',:orderNumber,'%') AND o.createTime>=:startCreateTime  AND o.createTime<=:endCreateTime  AND o.delFlag=1")
    Page<OrderVo> queryOrderList(Pageable pageable, @Param("storeId") String storeId, @Param("orderNumber") String orderNumber,
                                 @Param("startCreateTime") Date startCreateTime, @Param("endCreateTime") Date endCreateTime);

    //支付回调
    Order findByOrderNumberAndDelFlag(String orderNumber, Integer delFlag);


    //------------------------报表--------------------------------------------------
    //订单数量
    int countByStoreIdAndStatusAndDelFlag(String storeId, Integer status, Integer delFlag);

    //金额统计
    @Query(value = "select  sum(act_pay_money) from lysj_order_order where status=2 AND del_flag=1 AND  store_id=?1 AND   pay_way=?2", nativeQuery = true)
    BigDecimal summaryMoney(String storeId, Integer payWay);

    //财务统计
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,COUNT(*),SUM(act_pay_money) FROM lysj_order_order WHERE  status=2 AND del_flag=1 AND store_id=?1  " +
            "AND (create_time BETWEEN ?2 AND ?3)  GROUP BY days ", nativeQuery = true)
    List<Object[]> daylyStatic(String storeId, Date begin_date, Date end_date);

    //日营业统计
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,COUNT(*),SUM(cus_amount),SUM(dis_count_money),SUM(act_pay_money) FROM lysj_order_order WHERE  status=2 AND del_flag=1 AND store_id=?1  " +
            "AND (create_time BETWEEN ?2 AND ?3)  GROUP BY days ", nativeQuery = true)
    List<Object[]> businessStatic(String storeId, Date begin_date, Date end_date);

    //赠菜或退菜
    List<Order> findByStoreIdAndStatusAndDelFlagAndCreateTimeBetween(String storeId, Integer status, Integer delFlag, Date begin, Date end);

    //每日交接(获取现金收入、微信收入、今日总订单数、退款订单数、退款金额)
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,\n" +
            "(select sum(act_pay_money) from lysj_order_order WHERE  status=2 AND pay_way=1 AND del_flag=1 AND store_id=?1 AND (create_time BETWEEN ?2 AND ?3)) AS cash,\n" +
            "(select sum(act_pay_money) from lysj_order_order WHERE  status=2 AND pay_way=2 AND del_flag=1 AND store_id=?1 AND (create_time BETWEEN ?2 AND ?3)) AS wechatMoney,\n" +
            "COUNT(*) AS orderAmount,\n" +
            "(select COUNT(*) from lysj_order_order WHERE  status=3 AND del_flag=1 AND store_id=?1 AND (create_time BETWEEN ?2 AND ?3)) AS refundAmount,\n" +
            " (select sum(refund_pay_money) from lysj_order_order WHERE  status=3 AND del_flag=1 AND store_id=?1 AND (create_time BETWEEN ?2 AND ?3)) AS refundMoney\n" +
            "FROM lysj_order_order WHERE   del_flag=1 AND store_id=?1 AND (create_time BETWEEN ?2 AND ?3) GROUP BY days", nativeQuery = true)
    List<Object[]> handover(String storeId, Date begin_date, Date end_date);


    //查询门店今日订单数量
    Integer countByStoreIdAndStatusAndDelFlagAndCreateTimeBetween(String storeId, Integer status, Integer delFlag,Date beginDate, Date endDate);

    //统计门店今日营收
    @Query(value = "select  sum(act_pay_money) from lysj_order_order where store_id=?1 AND  status=2 AND del_flag=1 AND (create_time BETWEEN ?2 AND ?3)", nativeQuery = true)
    BigDecimal summaryDailyMoney(String storeId, Date beginDate, Date endDate);
}